{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# 安装postgres模块\n",
    "!pip install psycopg2-binary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connected to: PostgreSQL 11.14 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit\n"
     ]
    }
   ],
   "source": [
    "# 连接到 PostgresSQL\n",
    "import psycopg2\n",
    "\n",
    "conn = psycopg2.connect(\n",
    "    dbname=\"yzpang\",\n",
    "    user=\"postgres\",\n",
    "    password=\"3h1admin\",\n",
    "    host=\"192.168.1.234\",\n",
    "    port=\"5432\"\n",
    ")\n",
    "\n",
    "# 创建一个游标对象\n",
    "cur = conn.cursor()\n",
    "\n",
    "# 测试连接\n",
    "cur.execute(\"SELECT version();\")\n",
    "db_version = cur.fetchone()\n",
    "print(f\"Connected to: {db_version[0]}\")\n",
    "\n",
    "# 关闭游标和连接\n",
    "cur.close()\n",
    "conn.close()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "outputs": [],
   "source": [
    "# 创建表\n",
    "import psycopg2\n",
    "\n",
    "conn = psycopg2.connect(\n",
    "    dbname=\"yzpang\",\n",
    "    user=\"postgres\",\n",
    "    password=\"3h1admin\",\n",
    "    host=\"192.168.1.234\",\n",
    "    port=\"5432\"\n",
    ")\n",
    "\n",
    "# 创建一个游标对象\n",
    "cur = conn.cursor()\n",
    "\n",
    "\n",
    "# 创建表\n",
    "cur.execute(\"\"\"\n",
    "    CREATE TABLE IF NOT EXISTS users (\n",
    "        id SERIAL PRIMARY KEY,\n",
    "        name VARCHAR(100),\n",
    "        age INTEGER,\n",
    "        email VARCHAR(100) UNIQUE\n",
    "    )\n",
    "\"\"\")\n",
    "\n",
    "# 提交事务\n",
    "conn.commit()\n",
    "\n",
    "# 关闭游标和连接\n",
    "cur.close()\n",
    "conn.close()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "outputs": [],
   "source": [
    "# 插入数据\n",
    "import psycopg2\n",
    "\n",
    "conn = psycopg2.connect(\n",
    "    dbname=\"yzpang\",\n",
    "    user=\"postgres\",\n",
    "    password=\"3h1admin\",\n",
    "    host=\"192.168.1.234\",\n",
    "    port=\"5432\"\n",
    ")\n",
    "\n",
    "# 创建一个游标对象\n",
    "cur = conn.cursor()\n",
    "\n",
    "# 插入数据\n",
    "cur.execute(\"\"\"\n",
    "    insert into users (name, age, email)\n",
    "    values (%s, %s, %s)\n",
    "\"\"\", (\"Alice\", 30, \"alice@example.com\"))\n",
    "# 提交事务\n",
    "conn.commit()\n",
    "\n",
    "# 关闭游标和连接\n",
    "cur.close()\n",
    "conn.close()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(3, 'Alice', 30, 'alice@example.com')\n"
     ]
    }
   ],
   "source": [
    "# 读取数据\n",
    "import psycopg2\n",
    "\n",
    "# 连接到 PostgreSQL 服务器\n",
    "conn = psycopg2.connect(\n",
    "    dbname=\"yzpang\",\n",
    "    user=\"postgres\",\n",
    "    password=\"3h1admin\",\n",
    "    host=\"192.168.1.234\",\n",
    "    port=\"5432\"\n",
    ")\n",
    "\n",
    "# 创建一个游标对象\n",
    "cur = conn.cursor()\n",
    "\n",
    "# 读取数据\n",
    "cur.execute(\"SELECT * FROM users\")\n",
    "rows = cur.fetchall()\n",
    "for row in rows:\n",
    "    print(row)\n",
    "\n",
    "# 关闭游标和连接\n",
    "cur.close()\n",
    "conn.close()\n"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "outputs": [],
   "source": [
    "# 更新数据\n",
    "import psycopg2\n",
    "\n",
    "# 连接到 PostgreSQL 服务器\n",
    "conn = psycopg2.connect(\n",
    "    dbname=\"yzpang\",\n",
    "    user=\"postgres\",\n",
    "    password=\"3h1admin\",\n",
    "    host=\"192.168.1.234\",\n",
    "    port=\"5432\"\n",
    ")\n",
    "\n",
    "# 创建一个游标对象\n",
    "cur = conn.cursor()\n",
    "\n",
    "# 更新数据\n",
    "cur.execute(\"\"\"\n",
    "    UPDATE users\n",
    "    SET age = %s\n",
    "    WHERE name = %s\n",
    "\"\"\", (31, \"Alice\"))\n",
    "\n",
    "# 提交事务\n",
    "conn.commit()\n",
    "\n",
    "# 关闭游标和连接\n",
    "cur.close()\n",
    "conn.close()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "outputs": [],
   "source": [
    "# 删除数据\n",
    "# 更新数据\n",
    "import psycopg2\n",
    "\n",
    "# 连接到 PostgreSQL 服务器\n",
    "conn = psycopg2.connect(\n",
    "    dbname=\"yzpang\",\n",
    "    user=\"postgres\",\n",
    "    password=\"3h1admin\",\n",
    "    host=\"192.168.1.234\",\n",
    "    port=\"5432\"\n",
    ")\n",
    "\n",
    "# 创建一个游标对象\n",
    "cur = conn.cursor()\n",
    "\n",
    "# 删除数据\n",
    "cur.execute(\"DELETE FROM users WHERE name = %s\", (\"Alice\",))\n",
    "\n",
    "# 提交事务\n",
    "conn.commit()\n",
    "\n",
    "# 关闭游标和连接\n",
    "cur.close()\n",
    "conn.close()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}